package com.cloud.tsp.service.impl;

import cn.hutool.core.util.StrUtil;
import cn.hutool.poi.excel.ExcelReader;
import cn.hutool.poi.excel.ExcelUtil;
import cn.hutool.poi.excel.ExcelWriter;
import cn.hutool.poi.excel.StyleSet;
import com.alibaba.csp.sentinel.util.StringUtil;
import com.baomidou.mybatisplus.core.conditions.query.LambdaQueryWrapper;
import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper;
import com.baomidou.mybatisplus.core.metadata.IPage;
import com.baomidou.mybatisplus.extension.plugins.pagination.Page;
import com.baomidou.mybatisplus.extension.service.impl.ServiceImpl;
import com.cloud.api.annotation.LocalLock;
import com.cloud.core.exception.MyException;
import com.cloud.api.domain.forward.ResponseData;
import com.cloud.api.domain.model.VehicleModel;
import com.cloud.api.domain.tsp.*;
import com.cloud.api.utils.ExcelUtils;
import com.cloud.api.utils.ValidationUtils;
import com.cloud.api.constants.StrVo;
import com.cloud.mybatis.domain.TableDataInfo;
import com.cloud.tsp.mapper.VehicleProductionMapper;
import com.cloud.tsp.service.IEvModelInformationService;
import com.cloud.tsp.service.IVehicleModelService;
import com.cloud.tsp.service.IVehicleProductionService;
import com.cloud.tsp.service.IVehicleSaleService;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddressList;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.jetbrains.annotations.NotNull;
import org.springframework.beans.BeanUtils;
import org.springframework.context.MessageSource;
import org.springframework.context.i18n.LocaleContextHolder;
import org.springframework.stereotype.Service;
import org.springframework.web.multipart.MultipartFile;

import jakarta.annotation.Resource;
import jakarta.servlet.ServletOutputStream;
import jakarta.servlet.http.HttpServletResponse;
import jakarta.validation.ConstraintViolation;
import java.io.File;
import java.io.IOException;
import java.net.URLEncoder;
import java.text.MessageFormat;
import java.text.SimpleDateFormat;
import java.util.*;
import java.util.regex.Matcher;
import java.util.regex.Pattern;
import java.util.stream.Collectors;

import static com.cloud.tsp.service.impl.VehicleInformationServiceImpl.readExcelHeaders;

/**
 * <p>
 *  服务实现类
 * </p>
 *
 * @author haohaibo
 * @since 2023-12-20
 */
@Service("vehicleProductionService")
public class VehicleProductionServiceImpl extends ServiceImpl<VehicleProductionMapper, VehicleProduction> implements IVehicleProductionService {

    @Resource
    private VehicleProductionMapper vehicleProductionMapper;
    @Resource
    private IVehicleSaleService vehicleSaleService;
    @Resource
    private IVehicleModelService vehicleModelService;
    @Resource
    private IEvModelInformationService evModelInformationService;
    @Resource
    private MessageSource messageSource;

    @Override
    public TableDataInfo<VehicleProduction> selectProductionList(QueryVehicleProduction para) {
        Page<VehicleProduction> page = new Page<>();
        page.setCurrent(para.getPage());
        page.setSize(para.getPageSize());
        IPage<VehicleProduction> data = vehicleProductionMapper.selectProductionList(page, para);
        desensitize(data.getRecords());
        return TableDataInfo.build(data);
    }

    public void desensitize(List<VehicleProduction> list){
        for (VehicleProduction vehicleProduction : list) {
            vehicleProduction.setVin(StrUtil.hide(vehicleProduction.getVin(), 8, 11));
        }
    }

    @Override
    public VehicleProduction selectById(String id) {
        VehicleProduction vehicleProduction = vehicleProductionMapper.getById(id);
        String noticeModel = evModelInformationService.selectNoticeModelById(vehicleProduction.getModelType());
        vehicleProduction.setNoticeModel(noticeModel);
        return vehicleProduction;
    }

    @Override
    public VehicleProduction selectByVin(String vin) {
        return vehicleProductionMapper.selectByVin(vin);
    }
    @Override
    public VehicleProduction selectByVin1(String id) {
        return vehicleProductionMapper.selectByVin1(id);
    }

    @Override
    public List<String> getNameByVin(String vin) {
        List<String> list = new ArrayList<>();
        String name = null;
        VehicleProduction vehicleProduction = vehicleProductionMapper.getNameByVin(vin);
        if (vehicleProduction != null){
            name = vehicleProduction.getModelBrand() + "-" + vehicleProduction.getModelSeries() + "-" + vehicleProduction.getModelType();
            list.add(name);
            list.add(vehicleProduction.getDid());
        }
        return list;
    }

    @Override
    public void deleteById(String id) {
        VehicleProduction vehicleProduction = vehicleProductionMapper.selectById(id);
        if (!vehicleSaleService.checkExist(vehicleProduction.getVin())){
            throw new MyException("wds.exception.deleteProduction");
        }
        vehicleProductionMapper.deleteById(id);
    }

    @Override
    public void insertOrUpdate(VehicleProduction para) {
        if ("ev".equals(para.getVehicleType()) && StringUtil.isEmpty(para.getBatteryPackCode())){
            throw new MyException("validation.production.batteryPackCode");
        }
        if (StringUtil.isNotBlank(para.getId())){
            para.setVin(null);
        }
        if (!checkExist(para.getVin())){
            throw new MyException("wds.exception.vinRepeat");
        }
        this.saveOrUpdate(para);
    }

    public boolean checkExist(String vin) {
        LambdaQueryWrapper<VehicleProduction> wrapper = new QueryWrapper().lambda();
        wrapper.eq(VehicleProduction::getVin, vin);
        List<VehicleProduction> list = vehicleProductionMapper.selectList(wrapper);
        return list.size() == 0;
    }

    @Override
    public List<StrVo> selectProductionVinList(String vin) {
        Page<VehicleProduction> page = new Page<>(1,10);
        return vehicleProductionMapper.selectProductionVinList(page,vin);
    }

    @Override
    public void downloadTemplate(String vehicleType ,HttpServletResponse response) throws IOException {
        Locale locale = LocaleContextHolder.getLocale();
        String fileName = "table.productName";
        String message = messageSource.getMessage(fileName, null, fileName, locale);
        Map<String, String> map = translateFields(locale);

        ExcelWriter excelWriter = ExcelUtil.getWriter(message+".xlsx");
        excelWriter.setColumnWidth(0, 20);
        excelWriter.setColumnWidth(1, 25);
        excelWriter.setColumnWidth(2, 20);
        excelWriter.setColumnWidth(3, 20);
        excelWriter.setColumnWidth(4, 20);
        excelWriter.setColumnWidth(5, 20);
        excelWriter.setColumnWidth(6, 20);
        excelWriter.setColumnWidth(7, 20);
        excelWriter.setColumnWidth(8, 20);
        excelWriter.setColumnWidth(9, 33);
        excelWriter.setColumnWidth(10, 20);
        StyleSet styleSet = excelWriter.getStyleSet();

        styleSet.setBackgroundColor(IndexedColors.YELLOW1,false);
        if (vehicleType.equals("ev")){
            excelWriter.writeRow(Arrays.asList(map.get("num"),map.get("vin1"),map.get("model1"),map.get("motorNumber1"),map.get("batteryPackCode1"),map.get("tbox1"),map.get("did"),map.get("sim1"),map.get("iccid1"),map.get("productionDate1"),map.get("electricCode")));
        }else {
            excelWriter.writeRow(Arrays.asList(map.get("num"),map.get("vin1"),map.get("model1"),map.get("engineNumber1"),map.get("tbox1"),map.get("did"),map.get("sim1"),map.get("iccid1"),map.get("productionDate1")));
        }
        response.setContentType("application/vnd.ms-excel;charset=utf-8");
        response.setHeader("Content-Disposition", "attachment;filename*=" + URLEncoder.encode(message+".xlsx", "UTF-8"));
        try {
            Workbook workbook = excelWriter.getWorkbook();
            CellStyle cellStyle = workbook.createCellStyle();
            Sheet sheet = workbook.getSheetAt(0);
            Row row = sheet.getRow(0);
            Cell cell = row.getCell(2);
            String r = ((XSSFCell) cell).getCTCell().getR();
            r = r.substring(0, 1);
            // 设置数据验证
            DataValidationHelper helper = sheet.getDataValidationHelper();
            CellRangeAddressList addressList = new CellRangeAddressList(1, 600, 2, 2);
            //vin唯一
            DataValidationConstraint constraint = helper.createCustomConstraint(MessageFormat.format("COUNTIF({0}:{0},{0}2)=1",r));
            DataValidation dataValidation = helper.createValidation(constraint, addressList);
            dataValidation.createErrorBox("错误：", "vin不允许重复");
            dataValidation.setShowErrorBox(true);
            dataValidation.setEmptyCellAllowed(true);
            dataValidation.setSuppressDropDownArrow(true);
            dataValidation.setShowPromptBox(true);
            dataValidation.setErrorStyle(DataValidation.ErrorStyle.STOP);

            sheet.addValidationData(dataValidation);

            //设置列为文本格式
            DataFormat format = workbook.createDataFormat();
            cellStyle.setDataFormat(format.getFormat("@"));
            for (int i = 0; i < 8; i++) {
                excelWriter.setColumnStyle(i,cellStyle);
            }
            ServletOutputStream out=response.getOutputStream();
            workbook.write(out);
            workbook.close();
            out.close();
        } catch (IOException e) {
            e.printStackTrace();
        }
    }

    @Override
    public void exportProductionVinList(QueryVehicleProduction para, HttpServletResponse response) throws IOException {
        String vehicleType = para.getVehicleType();
        if (!"ev".equals(vehicleType) && !"oil".equals(vehicleType)) {
            throw new MyException("validation.production.vehicleType");
        }
        Locale locale = LocaleContextHolder.getLocale();
        String fileName = "table.productName";
        String message = messageSource.getMessage(fileName, null, fileName, locale);
        Map<String, String> map = translateFields(locale);
        ExcelWriter bigWriter = ExcelUtil.getBigWriter();
        List<VehicleProduction> list = vehicleProductionMapper.selectAllProductionList(para);

        SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
        list.forEach(a->{
            a.setVehicleType("ev".equals(a.getVehicleType())?"新能源":"油车");
            a.setVin(StrUtil.hide(a.getVin(), 8, 11));
            Date productionDate = a.getProductionDate();
            a.setProductionDateStr(sdf.format(productionDate));
        });
        bigWriter.setColumnWidth(0, 20);
        bigWriter.setColumnWidth(1, 20);
        bigWriter.setColumnWidth(2, 20);
        bigWriter.setColumnWidth(3, 20);
        bigWriter.setColumnWidth(4, 20);
        bigWriter.setColumnWidth(5, 20);
//        bigWriter.setColumnWidth(6, 20);
//        bigWriter.setColumnWidth(7, 20);
//        bigWriter.setColumnWidth(8, 20);
//        bigWriter.setColumnWidth(9, 20);
//        bigWriter.setColumnWidth(10, 20);
        bigWriter.addHeaderAlias("vehicleType", map.get("vehicleType"));
        bigWriter.addHeaderAlias("vin", map.get("vin"));
        bigWriter.addHeaderAlias("modelName", map.get("model"));
        bigWriter.addHeaderAlias("productionDateStr", map.get("productionDate"));
        /*if ("ev".equals(vehicleType)){
            bigWriter.addHeaderAlias("motorNumber", map.get("motorNumber"));
            bigWriter.addHeaderAlias("batteryPackCode", map.get("batteryPackCode"));
        }else {
            bigWriter.addHeaderAlias("engineNumber", map.get("engineNumber"));
        }*/
//        bigWriter.addHeaderAlias("tboxNumber", map.get("tbox"));
        bigWriter.addHeaderAlias("did", map.get("did"));
//        bigWriter.addHeaderAlias("simNumber", map.get("sim"));
//        bigWriter.addHeaderAlias("iccid", map.get("iccid"));
        bigWriter.addHeaderAlias("dept", map.get("organization"));
//        bigWriter.addHeaderAlias("noticeModel", map.get("noticeModel"));
        bigWriter.setOnlyAlias(true);
        CellStyle headCellStyle = bigWriter.getHeadCellStyle();
        headCellStyle.setFillBackgroundColor((short) 5);
        bigWriter.write(list);
        response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet;charset=utf-8");
//        response.setContentType("application/vnd.ms-excel;charset=utf-8");
        response.setHeader("Content-Disposition", "attachment;filename=" + URLEncoder.encode(message+".xlsx", "UTF8"));
        response.setCharacterEncoding("UTF-8");
        response.addHeader("Pargam", "no-cache");
        response.addHeader("Cache-Control", "no-cache");
        try {
            Workbook workbook = bigWriter.getWorkbook();
            ServletOutputStream out=response.getOutputStream();
            workbook.write(out);
            workbook.close();
            out.close();
            bigWriter.close();
        } catch (IOException e) {
            e.printStackTrace();
        }
    }

    @Override
    @LocalLock(key = "import",value = "#{key}", expire = 10000)
    public ResponseData importProductionVinList(String dept,String vehicleType, MultipartFile file, HttpServletResponse response, String key) throws IOException{
        if (dept == null){
            throw new MyException("wds.exception.deptNull");
        }
        Locale locale = LocaleContextHolder.getLocale();
        if ("ev".equals(vehicleType)){
            return importDataEV(dept, file, response, locale);
        }else {
            return importDataOil(dept, file, response, locale);
        }
    }


    private ResponseData importDataEV(String dept, MultipartFile file, HttpServletResponse response, Locale locale) throws IOException {
        //读取excel
        List<VehicleProductionExcel> vehicleProductions = parseExcel(file, locale);
        //定义导出集合
        List<VehicleProductionExcel> fail = new ArrayList<>();

        //检查excel中的vin是否重复
        List<String> repeat = checkData(vehicleProductions, fail);
        vehicleProductions.removeIf(vehicleProductionExcel -> repeat.contains(vehicleProductionExcel.getNum()));

        //注解校验,并移除添加到错误导出集合中
        checkValidation(vehicleProductions,fail, locale);

        List<VehicleProduction> saveList = new ArrayList<>();
        //保存没有问题的vin，用于数据库查询
        List<String> vinList = new ArrayList<>();
        //excel数据校验，筛选需要插入数据库的数据
        checkModel(vehicleProductions,saveList,vinList,fail, dept, locale);

        //查询数据库是否有重复,并将save中的数据移除到导出集合中
        chekDataSource(vinList,vehicleProductions,fail,saveList, locale);

        this.saveBatch(saveList);
        String fileName = null;
        if (fail.size() > 0){
            //导出fail
            fileName = exportErrList(fail, response);
        }
        ResponseData responseData = new ResponseData();
        responseData.setFileName(fileName);
        return responseData;
    }

    private ResponseData importDataOil(String dept, MultipartFile file, HttpServletResponse response, Locale locale) throws IOException {
        //读取excel
        List<VehicleProductionExcel1> vehicleProductions = parseExcelOil(file, locale);
        //定义导出集合
        List<VehicleProductionExcel1> fail = new ArrayList<>();

        //检查excel中的vin是否重复
        List<String> repeat = checkDataOil(vehicleProductions, fail);
        vehicleProductions.removeIf(vehicleProductionExcel -> repeat.contains(vehicleProductionExcel.getNum()));

        //注解校验,并移除添加到错误导出集合中
        checkValidationOil(vehicleProductions,fail, locale);

        List<VehicleProduction> saveList = new ArrayList<>();
        //保存没有问题的vin，用于数据库查询
        List<String> vinList = new ArrayList<>();
        //excel数据校验，筛选需要插入数据库的数据
        checkModelOil(vehicleProductions,saveList,vinList,fail, dept, locale);

        //查询数据库是否有重复,并将save中的数据移除到导出集合中
        chekDataSourceOil(vinList,vehicleProductions,fail,saveList, locale);

        this.saveBatch(saveList);
        String fileName = null;
        if (fail.size() > 0){
            //导出fail
            fileName = exportErrListOil(fail, response);
        }
        ResponseData responseData = new ResponseData();
        responseData.setFileName(fileName);
        return responseData;
    }

    @Override
    public List<String> getProductionModel() {
        return vehicleProductionMapper.getProductionModel();
    }

    @Override
    public List<VehicleProduction> getProductionCount() {
        return vehicleProductionMapper.getProductionCount();
    }

    public void chekDataSource(List<String>vinList,List<VehicleProductionExcel> vehicleProductions,List<VehicleProductionExcel> fail,List<VehicleProduction> saveList,Locale locale){
        if (vinList.size() == 0){
            return;
        }
        LambdaQueryWrapper<VehicleProduction> queryWrapper = new LambdaQueryWrapper<>();
        queryWrapper.in(VehicleProduction::getVin,vinList);
        List<VehicleProduction> list = vehicleProductionMapper.selectList(queryWrapper);
        List<String> repeatList = list.stream().map(VehicleProduction::getVin).collect(Collectors.toList());
        for (VehicleProductionExcel a : vehicleProductions){
            if (repeatList.contains(a.getVin())){
                String message = messageSource.getMessage("validation.sale.vinExist", null, locale);
                a.setErrResult(message);
                fail.add(a);
            }
        }
        //移除数据库中已经存在的数据
        saveList.removeIf(vehicleProduction -> repeatList.contains(vehicleProduction.getVin()));
    }

    public void chekDataSourceOil(List<String>vinList,List<VehicleProductionExcel1> vehicleProductions,List<VehicleProductionExcel1> fail,List<VehicleProduction> saveList,Locale locale){
        if (vinList.size() == 0){
            return;
        }
        LambdaQueryWrapper<VehicleProduction> queryWrapper = new LambdaQueryWrapper<>();
        queryWrapper.in(VehicleProduction::getVin,vinList);
        List<VehicleProduction> list = vehicleProductionMapper.selectList(queryWrapper);
        List<String> repeatList = list.stream().map(VehicleProduction::getVin).collect(Collectors.toList());
        for (VehicleProductionExcel1 a : vehicleProductions){
            if (repeatList.contains(a.getVin())){
                String message = messageSource.getMessage("validation.sale.vinExist", null, locale);
                a.setErrResult(message);
                fail.add(a);
            }
        }
        //移除数据库中已经存在的数据
        saveList.removeIf(vehicleProduction -> repeatList.contains(vehicleProduction.getVin()));
    }

    public void checkValidation(List<VehicleProductionExcel> vehicleProductions,List<VehicleProductionExcel> fail,Locale locale){
        List<VehicleProductionExcel> checkFail = new ArrayList<>();
        vehicleProductions.forEach(a ->{
            Set<ConstraintViolation<VehicleProductionExcel>> violations = ValidationUtils.getValidator().validate(a);
            List<String> error = new ArrayList<>();
            violations.forEach(b -> {
                String err = transErr(b.getMessage(), locale);
                error.add(err);
            });

            if(violations.size()>0){
                checkFail.add(a);
                a.setErrResult(error.toString());
                fail.add(a);
            }

        });

        vehicleProductions.removeAll(checkFail);
    }

    public void checkValidationOil(List<VehicleProductionExcel1> vehicleProductions,List<VehicleProductionExcel1> fail,Locale locale){
        List<VehicleProductionExcel1> checkFail = new ArrayList<>();
        vehicleProductions.forEach(a ->{
            Set<ConstraintViolation<VehicleProductionExcel1>> violations = ValidationUtils.getValidator().validate(a);
            List<String> error = new ArrayList<>();
            violations.forEach(b -> {
                String err = transErr(b.getMessage(), locale);
                error.add(err);
            });

            if(violations.size()>0){
                checkFail.add(a);
                a.setErrResult(error.toString());
                fail.add(a);
            }

        });

        vehicleProductions.removeAll(checkFail);
    }

    public void checkModel(List<VehicleProductionExcel> vehicleProductions,List<VehicleProduction> saveList,List<String> vinList,List<VehicleProductionExcel> fail,String dept,Locale locale){
        //获取匹配数据来源
        Map<String, Map<String, String>> data = getData();
        Map<String, String> relate = data.get("relate");

        for (VehicleProductionExcel a : vehicleProductions){
            String err = null;

            String vehicleType = "ev";
            Map<String, String> model = data.get(vehicleType);
            if (model == null){
                err = messageSource.getMessage("validation.production.noModel", null, locale);
                a.setErrResult(err);
                fail.add(a);
                continue;
            }
            String modelType = model.get(a.getModel() + vehicleType);

            if (modelType == null){
                err = messageSource.getMessage("validation.production.noModelType", null, locale);
                a.setErrResult(err);
                fail.add(a);
                continue;
            }
            VehicleProduction vehicleProduction = new VehicleProduction();
            BeanUtils.copyProperties(a,vehicleProduction);
            vehicleProduction.setModelType(modelType);
            vehicleProduction.setModelSeries(relate.get(vehicleProduction.getModelType()));
            vehicleProduction.setModelBrand(relate.get(vehicleProduction.getModelSeries()));
            vehicleProduction.setVehicleType(vehicleType);
            vehicleProduction.setDept(dept);
            saveList.add(vehicleProduction);
            vinList.add(vehicleProduction.getVin());
        }
    }

    public void checkModelOil(List<VehicleProductionExcel1> vehicleProductions,List<VehicleProduction> saveList,List<String> vinList,List<VehicleProductionExcel1> fail,String dept,Locale locale){
        //获取匹配数据来源
        Map<String, Map<String, String>> data = getData();
        Map<String, String> relate = data.get("relate");

        for (VehicleProductionExcel1 a : vehicleProductions){
            String err = null;

            String vehicleType = "oil";
            Map<String, String> model = data.get(vehicleType);
            if (model == null){
                err = messageSource.getMessage("validation.production.noModel", null, locale);
                a.setErrResult(err);
                fail.add(a);
                continue;
            }
            String modelType = model.get(a.getModel() + vehicleType);

            if (modelType == null){
                err = messageSource.getMessage("validation.production.noModelType", null, locale);
                a.setErrResult(err);
                fail.add(a);
                continue;
            }
            VehicleProduction vehicleProduction = new VehicleProduction();
            BeanUtils.copyProperties(a,vehicleProduction);
            vehicleProduction.setModelType(modelType);
            vehicleProduction.setModelSeries(relate.get(vehicleProduction.getModelType()));
            vehicleProduction.setModelBrand(relate.get(vehicleProduction.getModelSeries()));
            vehicleProduction.setVehicleType(vehicleType);
            vehicleProduction.setDept(dept);
            saveList.add(vehicleProduction);
            vinList.add(vehicleProduction.getVin());
        }
    }

    public Map<String,String> getVehicleType(){
        Map<String,String> vehicleTypes = new HashMap<>();
        vehicleTypes.put("新能源","ev");
        vehicleTypes.put("油车","oil");
        vehicleTypes.put("newEnergy","ev");
        vehicleTypes.put("oilTruck","oil");
        return vehicleTypes;
    }

    public List<VehicleProductionExcel> parseExcel(MultipartFile file,Locale locale) throws IOException {

        Map<String, String> map = translateFields(locale);
        List<String> excelHeaders = readExcelHeaders(file);
        List<String> header = Arrays.asList(map.get("num"),map.get("vin1"),map.get("model1"),map.get("motorNumber1"),map.get("batteryPackCode1"),map.get("tbox1"),map.get("did"),map.get("sim1"),map.get("iccid1"),map.get("productionDate1"),map.get("electricCode"));
        if (!header.equals(excelHeaders)) {
            throw new MyException("wds.exception.importHeaderError");
        }

        ExcelReader reader = ExcelUtil.getReader(file.getInputStream());
        reader.addHeaderAlias(map.get("vehicleType1"),"vehicleType")
                .addHeaderAlias(map.get("vin1"),"vin")
                .addHeaderAlias(map.get("num"),"num")
                .addHeaderAlias( map.get("model1"),"model")
                .addHeaderAlias(map.get("productionDate1"),"productionDate")
                .addHeaderAlias(map.get("motorNumber1"),"motorNumber")
                .addHeaderAlias(map.get("batteryPackCode1"),"batteryPackCode")
                .addHeaderAlias(map.get("tbox1"),"tboxNumber")
                .addHeaderAlias(map.get("did"),"did")
                .addHeaderAlias(map.get("sim1"),"simNumber")
                .addHeaderAlias(map.get("iccid1"),"iccid")
                .addHeaderAlias(map.get("electricCode"),"electricCode");

        List<VehicleProductionExcel> vehicleProductions = reader.readAll(VehicleProductionExcel.class);
        if (vehicleProductions.size() > 500){
            throw new MyException("wds.exception.importData");
        }
        return vehicleProductions;
    }

    public List<VehicleProductionExcel1> parseExcelOil(MultipartFile file,Locale locale) throws IOException {

        Map<String, String> map = translateFields(locale);
        List<String> excelHeaders = readExcelHeaders(file);
        List<String> header = Arrays.asList(map.get("num"),map.get("vin1"),map.get("model1"),map.get("engineNumber1"),map.get("tbox1"),map.get("did"),map.get("sim1"),map.get("iccid1"),map.get("productionDate1"));
        if (!header.equals(excelHeaders)) {
            throw new MyException("wds.exception.importHeaderError");
        }

        ExcelReader reader = ExcelUtil.getReader(file.getInputStream());
        reader.addHeaderAlias(map.get("vehicleType1"),"vehicleType")
                .addHeaderAlias(map.get("vin1"),"vin")
                .addHeaderAlias(map.get("num"),"num")
                .addHeaderAlias( map.get("model1"),"model")
                .addHeaderAlias(map.get("productionDate1"),"productionDate")
                .addHeaderAlias(map.get("engineNumber1"),"motorNumber")
                .addHeaderAlias(map.get("tbox1"),"tboxNumber")
                .addHeaderAlias(map.get("did"),"did")
                .addHeaderAlias(map.get("sim1"),"simNumber")
                .addHeaderAlias(map.get("iccid1"),"iccid");

        List<VehicleProductionExcel1> vehicleProductions = reader.readAll(VehicleProductionExcel1.class);
        if (vehicleProductions.size() > 500){
            throw new MyException("wds.exception.importData");
        }
        return vehicleProductions;
    }

    public String transErr(String err,Locale locale){
        Pattern pattern = Pattern.compile("\\{(.*?)\\}");
        Matcher matcher = pattern.matcher(err);
        if (matcher.find()){
            return messageSource.getMessage(matcher.group(1), null, matcher.group(1), locale);
        }
        return err;
    }

    public Map<String,Map<String,String>> getData(){
        Map<String,String> evModel = new HashMap<>();
        Map<String,String> oilModel = new HashMap<>();
        Map<String,String> relate = new HashMap<>();
        List<VehicleModel> models = vehicleModelService.list();
        if (models == null){
            throw new MyException("wds.exception.importModelNull");
        }
        models.forEach(a -> {
            if ("ev".equals(a.getType())){
                evModel.put(a.getModelName()+"ev",a.getId());
            }else {
                oilModel.put(a.getModelName()+"oil",a.getId());
            }
            relate.put(a.getId(),a.getParentId());
        });
        Map<String,Map<String,String>> map = new HashMap<>();
        map.put("ev",evModel);
        map.put("oil",oilModel);
        map.put("relate",relate);
        return map;
    }

    public List<String> checkData(List<VehicleProductionExcel> para,List<VehicleProductionExcel> fail){
        Locale locale = LocaleContextHolder.getLocale();
        List<String> repeatList = new ArrayList<>();
        Map<String, Integer> map = new HashMap<>();
        for (VehicleProductionExcel v : para) {
            if (map.containsKey(v.getVin())) {
                repeatList.add(v.getNum());
                Integer num = map.get(v.getVin());
                map.put(v.getVin(), num + 1);
                String message = messageSource.getMessage("validation.sale.ExcelRepeatVin", null, locale);
                v.setErrResult(message);
                fail.add(v);
            } else {
                map.put(v.getVin(), 1);
            }
        }
        return repeatList;
    }
    public List<String> checkDataOil(List<VehicleProductionExcel1> para,List<VehicleProductionExcel1> fail){
        Locale locale = LocaleContextHolder.getLocale();
        List<String> repeatList = new ArrayList<>();
        Map<String, Integer> map = new HashMap<>();
        for (VehicleProductionExcel1 v : para) {
            if (map.containsKey(v.getVin())) {
                repeatList.add(v.getNum());
                Integer num = map.get(v.getVin());
                map.put(v.getVin(), num + 1);
                String message = messageSource.getMessage("validation.sale.ExcelRepeatVin", null, locale);
                v.setErrResult(message);
                fail.add(v);
            } else {
                map.put(v.getVin(), 1);
            }
        }
        return repeatList;
    }

    //导出错误数据
    public String exportErrList(List<VehicleProductionExcel> list,HttpServletResponse response) throws IOException {
        /*Locale locale = LocaleContextHolder.getLocale();
        String fileName = "table.productNameErr";
        String message = messageSource.getMessage(fileName, null, fileName, locale);
        Map<String, String> map = translateFields(locale);
        ExcelWriter bigWriter = ExcelUtil.getBigWriter();
        bigWriter.setColumnWidth(0, 20);
        bigWriter.setColumnWidth(1, 20);
        bigWriter.setColumnWidth(2, 20);
        bigWriter.setColumnWidth(3, 20);
        bigWriter.setColumnWidth(4, 20);
        bigWriter.setColumnWidth(5, 20);
        bigWriter.setColumnWidth(6, 20);
        bigWriter.setColumnWidth(7, 20);
        bigWriter.setColumnWidth(8, 20);
        bigWriter.setColumnWidth(9, 20);
        bigWriter.setColumnWidth(10, 20);
        bigWriter.addHeaderAlias("errResult", map.get("errResult"));
        bigWriter.addHeaderAlias("num", map.get("num"));
        bigWriter.addHeaderAlias("vehicleType", map.get("vehicleType1"));
        bigWriter.addHeaderAlias("vin", map.get("vin1"));
        bigWriter.addHeaderAlias("model", map.get("model"));
        bigWriter.addHeaderAlias("motorNumber", map.get("motorNumber1"));
        bigWriter.addHeaderAlias("batteryPackCode", map.get("batteryPackCode1"));
        bigWriter.addHeaderAlias("tboxNumber", map.get("tbox1"));
        bigWriter.addHeaderAlias("simNumber", map.get("sim1"));
        bigWriter.addHeaderAlias("iccid", map.get("iccid1"));
        bigWriter.addHeaderAlias("productionDate", map.get("productionDate1"));
        bigWriter.setOnlyAlias(true);
        CellStyle headCellStyle = bigWriter.getHeadCellStyle();
        headCellStyle.setFillBackgroundColor((short) 5);
        bigWriter.write(list);
//        response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet;charset=utf-8");
//        response.setContentType("application/octet-stream");
        response.setContentType("application/vnd.ms-excel;charset=utf-8");
        response.setHeader("Content-Disposition", "attachment;filename=" + URLEncoder.encode(message+".xlsx", "UTF-8"));
        response.setCharacterEncoding("UTF-8");
        response.addHeader("Pargam", "no-cache");
        response.addHeader("Cache-Control", "no-cache");
        try {
            Workbook workbook = bigWriter.getWorkbook();
            ServletOutputStream out=response.getOutputStream();
            workbook.write(out);
            workbook.close();
            out.close();
            bigWriter.close();
        } catch (IOException e) {
            e.printStackTrace();
        }*/
        String basePath = "/data/cglx/errFiles/";
        String fileName = UUID.randomUUID().toString()+".xlsx";
        ExcelUtils.export(new File(basePath+fileName),list, VehicleProductionExcel.class);
        return fileName;
    }

    public String exportErrListOil(List<VehicleProductionExcel1> list,HttpServletResponse response) throws IOException {
        String basePath = "/data/cglx/errFiles/";
        String fileName = UUID.randomUUID().toString()+".xlsx";
        ExcelUtils.export(new File(basePath+fileName),list, VehicleProductionExcel1.class);
        return fileName;
    }

    public Map<String, String> translateFields(Locale locale){
        Map<String,String> map = new HashMap<>();
        String vin1 = "table.field.*vin";
        String vin = "table.field.vin";
        String num = "table.field.num";
        String model = "table.field.model";
        String model1 = "table.field.*model";
        String productionDate = "table.field.productionDate";
        String productionDate1 = "table.field.*productionDate";
        String motorNumber = "table.field.motorNumber";
        String motorNumber1 = "table.field.*motorNumber";
        String engineNumber = "table.field.engineNumber";
        String engineNumber1 = "table.field.*engineNumber";
        String batteryPackCode = "table.field.batteryPackCode";
        String batteryPackCode1 = "table.field.*batteryPackCode";
        String tbox = "table.field.tbox";
        String tbox1 = "table.field.*tbox";
        String sim = "table.field.sim";
        String sim1 = "table.field.*sim";
        String iccid = "table.field.iccid";
        String iccid1 = "table.field.*iccid";
        String vehicleType = "table.field.vehicleType";
        String vehicleType1 = "table.field.*vehicleType";
        String noticeModel = "table.field.noticeModel";
        String errResult = "table.field.errResult";
        String organization = "table.field.organization";
        String did = "table.field.did";
        String electricCode1 = "table.field.*electricCode";
        String electricCode = "table.field.electricCode";


        map.put("num",messageSource.getMessage(num, null, num, locale));
        map.put("vin",messageSource.getMessage(vin, null, vin, locale));
        map.put("vin1",messageSource.getMessage(vin1, null, vin1, locale));
        map.put("model",messageSource.getMessage(model, null, model, locale));
        map.put("model1",messageSource.getMessage(model1, null, model1, locale));
        map.put("tbox",messageSource.getMessage(tbox, null, tbox, locale));
        map.put("tbox1",messageSource.getMessage(tbox1, null, tbox1, locale));
        map.put("sim",messageSource.getMessage(sim, null, sim, locale));
        map.put("sim1",messageSource.getMessage(sim1, null, sim1, locale));
        map.put("iccid",messageSource.getMessage(iccid, null, iccid, locale));
        map.put("iccid1",messageSource.getMessage(iccid1, null, iccid1, locale));
        map.put("productionDate",messageSource.getMessage(productionDate, null, productionDate, locale));
        map.put("productionDate1",messageSource.getMessage(productionDate1, null, productionDate1, locale));
        map.put("motorNumber",messageSource.getMessage(motorNumber, null, motorNumber, locale));
        map.put("motorNumber1",messageSource.getMessage(motorNumber1, null, motorNumber1, locale));
        map.put("engineNumber",messageSource.getMessage(engineNumber, null, engineNumber, locale));
        map.put("engineNumber1",messageSource.getMessage(engineNumber1, null, engineNumber1, locale));
        map.put("batteryPackCode",messageSource.getMessage(batteryPackCode, null, batteryPackCode, locale));
        map.put("batteryPackCode1",messageSource.getMessage(batteryPackCode1, null, batteryPackCode1, locale));
        map.put("vehicleType",messageSource.getMessage(vehicleType, null, vehicleType, locale));
        map.put("vehicleType1",messageSource.getMessage(vehicleType1, null, vehicleType1, locale));
        map.put("noticeModel",messageSource.getMessage(noticeModel, null, noticeModel, locale));
        map.put("errResult",messageSource.getMessage(errResult, null, errResult, locale));
        map.put("organization",messageSource.getMessage(organization, null, organization, locale));
        map.put("did",messageSource.getMessage(did, null, did, locale));
        map.put("electricCode1",messageSource.getMessage(electricCode1, null, electricCode1, locale));
        map.put("electricCode",messageSource.getMessage(electricCode, null, electricCode, locale));
        return map;
    }
}